package com.fish.yssb.db.excel;

import android.content.Context;
import android.content.res.AssetManager;
import android.util.Log;

import com.fish.yssb.db.CustomMigration;
import com.fish.yssb.db.RealmDb;
import com.fish.yssb.db.bean.BaseInfoTable;

import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import io.realm.Realm;
import io.realm.RealmConfiguration;
import io.realm.RealmObject;
import io.realm.RealmResults;
import io.realm.Sort;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;


/**
 * Used 尺寸转换工具类（全）
 */

/**
 * 读取Excel
 */
public class ReadExcel {

    public static void getData(Context context) {
        // 字符列表

        List<String> list = new ArrayList<String>();
        // 文件路径
        // 输入流
        InputStream is = null;
        // Excel工作簿
        Workbook workbook = null;

        AssetManager am = context.getResources().getAssets();
        try {
            InputStream in = am.open("model2.xls");
            // 获取workbook
            workbook = Workbook.getWorkbook(in);
        } catch (Exception ex) {
            ex.printStackTrace();
        }

        ArrayList<BaseInfoTable> dbList = new ArrayList<>();
        RealmDb.getInstance().deleteAllRealmObjects();
        Cell cell = null;// 单个单元格

        // 获取sheet, 如果你的workbook里有多个sheet可以利用workbook.getSheets()方法来得到所有的
        Sheet sheet = workbook.getSheet(0);// 这里只取得第一个sheet的值，默认从0开始
        // 开始循环，取得cell里的内容，按具体类型来取

        //遥信
        int sortnum =10000;
        for (int j = 1;j<sheet.getRows();j++){
            BaseInfoTable info = new BaseInfoTable();
            info.setParaType(1);
            info.setChange(false);
            for (int i=0;i<sheet.getColumns();i++){
                // 列,行
                cell = sheet.getCell(i, j);
                if(i==1 && "".equalsIgnoreCase(cell.getContents().trim()))
                {
                    break;
                }

                switch (i)
                {
                    case 0:
                        info.setIdSort(sortnum +j);

                        break;
                    case 1:
                        info.setName(cell.getContents());
                        break;
                    case 2:
                        info.setConnAdr(cell.getContents());
                        break;
                    case 3:
                        info.setUnit(cell.getContents());
                        break;
                    case 4:
                        // 0.无 1.布尔 2.浮点 3.整形 4.字节
                        int type = 0;
                        if("单点".equalsIgnoreCase(cell.getContents()))
                        {
                            type = 1;
                        }else if("浮点".equalsIgnoreCase(cell.getContents()))
                        {
                            type = 2;
                        }else if("整形".equalsIgnoreCase(cell.getContents()))
                        {
                            type = 3;
                        }else if("字节".equalsIgnoreCase(cell.getContents()))
                        {
                            type = 4;
                        }else{
                            type= 0;
                        }
                        info.setDataType(type);
                        break;
                }

            }
            if(info.getName()!=null && !"".equalsIgnoreCase(info.getName().trim())) {
                dbList.add(info);
            }
        }

        sheet = workbook.getSheet(1);// 这里只取得第一个sheet的值，默认从0开始
        // 开始循环，取得cell里的内容，按具体类型来取
        //1 遥信 2遥测 3遥脉 4遥控 5 参数设定
        //2遥测
        sortnum =20000;
        for (int j = 1;j<sheet.getRows();j++){
            BaseInfoTable info = new BaseInfoTable();
            info.setParaType(2);
            info.setChange(false);
            for (int i=0;i<sheet.getColumns();i++){
                // 列,行
                cell = sheet.getCell(i, j);
                if(i==1 && "".equalsIgnoreCase(cell.getContents().trim()))
                {
                    break;
                }

                switch (i)
                {
                    case 0:
                        info.setIdSort(sortnum +j);
                        break;
                    case 1:
                        info.setName(cell.getContents());
                        break;
                    case 2:
                        info.setConnAdr(cell.getContents());
                        break;
                    case 3:
                        info.setUnit(cell.getContents());
                        break;
                    case 4:
                        // 0.无 1.布尔 2.浮点 3.整形 4.字节
                        int type = 0;
                        if("单点".equalsIgnoreCase(cell.getContents()))
                        {
                            type = 1;
                        }else if("浮点".equalsIgnoreCase(cell.getContents()))
                        {
                            type = 2;
                        }else if("整形".equalsIgnoreCase(cell.getContents()))
                        {
                            type = 3;
                        }else if("字节".equalsIgnoreCase(cell.getContents()))
                        {
                            type = 4;
                        }else{
                            type= 0;
                        }
                        info.setDataType(type);
                        break;
                }

            }
            if(info.getName()!=null && !"".equalsIgnoreCase(info.getName().trim())) {
                dbList.add(info);
            }
        }

        sheet = workbook.getSheet(2);// 这里只取得第一个sheet的值，默认从0开始
        // 开始循环，取得cell里的内容，按具体类型来取
        //1 遥信 2遥测 3遥脉 4遥控 5 参数设定
        //2遥脉
        sortnum =30000;
        for (int j = 1;j<sheet.getRows();j++){
            BaseInfoTable info = new BaseInfoTable();
            info.setParaType(3);
            info.setChange(false);
            for (int i=0;i<sheet.getColumns();i++){
                // 列,行
                cell = sheet.getCell(i, j);
                if(i==1 && "".equalsIgnoreCase(cell.getContents().trim()))
                {
                    break;
                }

                switch (i)
                {
                    case 0:
                        info.setIdSort(sortnum +j);
                        break;
                    case 1:
                        info.setName(cell.getContents());
                        break;
                    case 2:
                        info.setConnAdr(cell.getContents());
                        break;
                    case 3:
                        info.setUnit(cell.getContents());
                        break;
                    case 4:
                        // 0.无 1.布尔 2.浮点 3.整形 4.字节
                        int type = 0;
                        if("单点".equalsIgnoreCase(cell.getContents()))
                        {
                            type = 1;
                        }else if("浮点".equalsIgnoreCase(cell.getContents()))
                        {
                            type = 2;
                        }else if("整形".equalsIgnoreCase(cell.getContents()))
                        {
                            type = 3;
                        }else if("字节".equalsIgnoreCase(cell.getContents()))
                        {
                            type = 4;
                        }else{
                            type= 0;
                        }
                        info.setDataType(type);
                        break;
                }

            }
            if(info.getName()!=null && !"".equalsIgnoreCase(info.getName().trim())) {
                dbList.add(info);
            }
        }

        sheet = workbook.getSheet(3);// 这里只取得第一个sheet的值，默认从0开始
        // 开始循环，取得cell里的内容，按具体类型来取
        //1 遥信 2遥测 3遥脉 4遥控 5 参数设定
        //4遥控
        sortnum =30000;
        for (int j = 1;j<sheet.getRows();j++){
            BaseInfoTable info = new BaseInfoTable();
            info.setParaType(4);
            info.setChange(false);
            for (int i=0;i<sheet.getColumns();i++){
                // 列,行
                cell = sheet.getCell(i, j);
                if(i==1 && "".equalsIgnoreCase(cell.getContents().trim()))
                {
                    break;
                }

                switch (i)
                {
                    case 0:
                        info.setIdSort(sortnum +j);
                        break;
                    case 1:
                        info.setName(cell.getContents());
                        break;
                    case 2:
                        info.setConnAdr(cell.getContents());
                        break;
                    case 3:
                        info.setUnit(cell.getContents());
                        break;
                    case 4:
                        // 0.无 1.布尔 2.浮点 3.整形 4.字节
                        int type = 0;
                        if("单点".equalsIgnoreCase(cell.getContents()))
                        {
                            type = 1;
                        }else if("浮点".equalsIgnoreCase(cell.getContents()))
                        {
                            type = 2;
                        }else if("整形".equalsIgnoreCase(cell.getContents()))
                        {
                            type = 3;
                        }else if("字节".equalsIgnoreCase(cell.getContents()))
                        {
                            type = 4;
                        }else{
                            type= 0;
                        }
                        info.setDataType(type);
                        break;
                }

            }
            if(info.getName()!=null && !"".equalsIgnoreCase(info.getName().trim())) {
                dbList.add(info);
            }
        }

        sheet = workbook.getSheet(4);// 这里只取得第一个sheet的值，默认从0开始
        // 开始循环，取得cell里的内容，按具体类型来取
        //1 遥信 2遥测 3遥脉 4遥控 5 参数设定
        //4遥控
        sortnum =40000;
        for (int j = 1;j<sheet.getRows();j++){
            BaseInfoTable info = new BaseInfoTable();
            info.setParaType(5);
            info.setChange(false);
            for (int i=0;i<sheet.getColumns();i++){
                // 列,行
                cell = sheet.getCell(i, j);
                if(i==1 && "".equalsIgnoreCase(cell.getContents().trim()))
                {
                    break;
                }

                switch (i)
                {
                    case 0:
                        info.setIdSort(sortnum +j);
                        break;
                    case 1:
                        info.setName(cell.getContents());
                        break;
                    case 2:
                        info.setConnAdr(cell.getContents());
                        break;
                    case 3:
                        info.setUnit(cell.getContents());
                        break;
                    case 4:
                        // 0.无 1.布尔 2.浮点 3.整形 4.字节
                        int type = 0;
                        if("单点".equalsIgnoreCase(cell.getContents()))
                        {
                            type = 1;
                        }else if("浮点".equalsIgnoreCase(cell.getContents()))
                        {
                            type = 2;
                        }else if("整形".equalsIgnoreCase(cell.getContents()))
                        {
                            type = 3;
                        }else if("字节".equalsIgnoreCase(cell.getContents()))
                        {
                            type = 4;
                        }else{
                            type= 0;
                        }
                        info.setDataType(type);
                        break;
                }

            }
            if(info.getName()!=null && !"".equalsIgnoreCase(info.getName().trim())) {
                dbList.add(info);
            }
        }
        RealmDb.getInstance().insertRealmObjects(dbList);
        List<BaseInfoTable> arrayList = (List<BaseInfoTable>) RealmDb.getInstance().queryRealmObjects(BaseInfoTable.class);

        Log.e("=========","count:"+arrayList.size());

        workbook.close();// 关闭工作簿

        // 迭代集合查看每行的数据
        for (String ss : list){
            System.out.println(ss);
        }
    }
}